# coding=utf-8
# -*- coding: utf-8 -*-
import sys
import traceback
from datetime import datetime
import numpy as np
import pymongo
from pandas import DataFrame
from pymongo import MongoClient
from simplemysql import SimpleMysql
import Utils

reload(sys)
sys.setdefaultencoding('utf-8')

mgClient = MongoClient('121.40.54.235', 27017,
    username='root2',
    password='Dream2015',
    authSource="stockDataStore",
    authMechanism='SCRAM-SHA-1')
db = mgClient['stockDataStore']

mysqlDB = SimpleMysql(
    host="121.40.54.235",
    db="stock-grab",
    user="root",
    passwd="Dream2015",
    charset="utf8",
    keep_alive=True  # try and reconnect timedout mysql connections?
)


def get_finance_data(table, stock_code):
    cursor = (db[table].find({"code": stock_code}).sort('period', pymongo.ASCENDING))
    all_period_data = list(cursor)
    if len(all_period_data) == 0:
        return None
    #以最后一个记录为profile
    item_names = all_period_data[-1].keys()
    item_names = filter(lambda x: x != '_id' and x != 'period', item_names)
    periods = [];
    data_table = []
    for period_data in all_period_data:
        periods.append(period_data[u'period'])
        datatable_column_data = []
        for item_name in item_names:
            datatable_column_data.append(period_data.get(item_name, None))
        data_table.append(datatable_column_data)
    df = DataFrame(data_table, columns=item_names, index=periods).transpose()
    return df


def get_stock_finance_data_dataframe(code):
    bs_df = get_finance_data("balance_sheet", code)
    ps_df = get_finance_data("profit_statement", code)
    cf_df = get_finance_data("cash_flow", code)
    if bs_df is None or ps_df is None or cf_df is None:
        Utils.logger.warn("没有财务报表数据 " + code)
        return None;
    if u'净利润' in cf_df.index:
        cf_df = cf_df.drop([u'净利润'])
    if u'财务费用' in cf_df.index:
        cf_df = cf_df.drop([u'财务费用']) ##利润表也有净利润，在合并前，把现金流量表里删除掉,防止计算时候多列导致的错误
    # combined_df = bs_df.append(ps_df, sort=True).append(cf_df, sort=True)
    combined_df = bs_df.append(ps_df).append(cf_df)
    if combined_df.shape[0] != (bs_df.shape[0] + ps_df.shape[0] + cf_df.shape[0]):
        Utils.logger.warn("合并后的dataframe有问题")
        return None
    return combined_df


def calc_average_of_year(df, item_name, average_item_name):
    df[average_item_name] = (df[item_name] + df[item_name].shift(3)) / 2
    ##最前面3条是NaN值，平均值以原先单季度值代替
    df.iloc[0:3, df.columns.get_loc(average_item_name)] = df.iloc[0:3, df.columns.get_loc(item_name)]


def calc_stock_percent_change(df):
    tmp = df.applymap(lambda x: 1 if x > 0 else -1).shift(4)
    df = zero_to_nan_for_df(df)
    percent_df = df.pct_change(periods=4, fill_method=None) * tmp
    return percent_df


def zero_to_nan(series):
    ##return series.apply(lambda x: None if x == 0 else x)
    return series.replace({0: np.nan})

def zero_to_nan_for_df(df):
    #return df.applymap(lambda x: None if x == 0 else x)
    return df.replace({0: np.nan})



#包括银行、证券、保险 的报表和普通的行业不一样，通过几个财务item来区分
def check_if_financial_stock(df):
    if u'营业收入' in df.columns and u'营业支出' in df.columns:
        return True
    return False;

def calc_stock_indices(code):
    df = get_stock_finance_data_dataframe(code)
    if df is None:
        Utils.logger.warn("获取财务数据失败，可能是系统没有数据：stock code = " + code)
        return None
    df = df.transpose()

    is_financial_stock = check_if_financial_stock(df)

    if is_financial_stock:
        Utils.logger.warn("finance stock:" + code)

    '''
    if u'现金及存放中央银行款项' in df.columns and u'向中央银行借款' in df.columns:
        Utils.logger.warn("金融股和证券股（中信证券、国金证券）暂且不计算 " + code)
        return None
    '''

    '''
    有息负债=短期借款+长期借款+应付债券+一年之内到期的非流动负债+少数股东权益
    ROIC = (营业利润+财务费用-非经常性投资收益)(1-所得税/(净利润 + 所得税))/权益净资产+有息负债 - 超额现金及非经营性资产)
    因为1. 非经常性投资收益 2.超额现金及非经营性资产 财务报表没有相关而且一般企业也不是占比都比较少所以忽略'''

    ''''**
    *http: // blog.sina.com.cn / s / blog_59b90d8f0100bf99.html
    *ROIC = NOPLAT / IC
    NOPLAT一般称作“税后经营利润”，有教材称作“息前税后利润”，是指息税前利润EBIT扣除经营所得税得到的。
    IC是投入资本，包括各种有息负债、权益资本。
    NOPLAT = EBIT * (1 - T)
    EBIT = 营业利润 + 财务费用 - 非经常性投资收益
    T(税率) = 所得税 / (净利润 + 所得税)
    投入资本IC包括：权益净资产及有息负债，同时应该扣除掉超额现金及非经营性资产。
    *有息负债 = 短期借款 + 长期借款 + 应付债券 + 一年之内到期的非流动负债 + 少数股东权益
    *ROIC = (营业利润 + 财务费用 - 非经常性投资收益)(1 - 所得税 / (净利润 + 所得税)) / 权益净资产 + 有息负债 - 超额现金及非经营性资产)
    *因为1.非经常性投资收益
    2.
    超额现金及非经营性资产
    财务报表没有相关而且一般企业也不是占比都比较少所以忽略
    '''

    bs_total_liability_and_equity = '负债及股东权益总计'
    bs_total_equity = '股东权益合计'
    df['total_assets'] = df[unicode(bs_total_liability_and_equity)]
    df['net_assets'] = df[unicode(bs_total_equity)]
    df['net_assets_opc'] = df[unicode('归属于母公司股东权益合计')]
    df['net_profit'] = df[unicode('净利润')]
    df['net_profit_opc'] = df[unicode('归属于母公司股东的净利润')]
    df['net_profit_koufei'] = df[unicode('扣非净利润')]
    df['main_biz_income'] = df[unicode('营业收入')]
    if is_financial_stock:
        df['main_biz_cost'] = df[unicode('营业支出')]
    else:
        df['main_biz_cost'] = df[unicode('营业成本')]

    if not is_financial_stock:
        df['selling_expenses'] = df[unicode('销售费用')]
        df['financial_expenses'] = df[unicode('财务费用')]
        df['management_expenses'] = df[unicode('管理费用')]

    df['total_shares'] = df[unicode('股本')] * 10000

    # 盈利能力
    df['roe'] = df[unicode('净利润')] / zero_to_nan(df['net_assets'])
    df['roa'] = df[unicode('净利润')] / zero_to_nan(df['total_assets'])

    if not is_financial_stock:
        df['EBIT'] = (df[unicode('营业利润')] + df[unicode('财务费用')])
        df['T'] = df[unicode('所得税费用')] / zero_to_nan(df[unicode('净利润')] + df[unicode('所得税费用')])
        df[unicode('有息负债')] = df[unicode('短期借款')] + df[unicode('长期借款')] + df[unicode('应付债券')]
        df['NOPLAT'] = df['EBIT'] * (1 - df['T'])
        df['IC'] = (df['net_assets'] + df[unicode('有息负债')])
        df['roic'] = df['NOPLAT'] / zero_to_nan(df['IC'])
        df['gpr'] = (df[unicode('营业收入')] - df[unicode('营业成本')]) / zero_to_nan(df[unicode('营业收入')])
    df['npr'] = df[unicode('净利润')] / zero_to_nan(df[unicode('营业收入')])

    # 成长能力
    percent_calc_df = df[[u'营业收入', u'净利润', unicode(bs_total_equity), unicode(bs_total_liability_and_equity)]]
    df[['mbg_rate', 'npg_rate', 'nag_rate', 'tag_rate']] = calc_stock_percent_change(percent_calc_df)

    # 运营能力
    '''
    应收账款周转率＝主营业务收入净额÷应收账款平均余额(http://wiki.mbalib.com/wiki/%E5%BA%94%E6%94%B6%E8%B4%A6%E6%AC%BE%E5%91%A8%E8%BD%AC%E7%8E%87)
    存货周转率（次）＝销售（营业）成本÷平均存货
    平均存货＝（年初存货+年末存货）÷2
    存货周转率（天）＝360÷存货周转率（次）
    '''

    # 应收账款周转率（次）
    if not is_financial_stock:
        calc_average_of_year(df, u'应收账款', 'ar_average')
        df['ar_turnover'] = df[unicode('营业收入')] / zero_to_nan(df['ar_average'])
        df['ar_turnover'] = df['ar_turnover'].apply(lambda x: None if x == 0 else x)
        # 应收账款周转天数(天)
        df['ar_days'] = 360 / df['ar_turnover']

    # 存货周转率（次)
    if not is_financial_stock:
        calc_average_of_year(df, u'存货', 'average_inventory')
        df['inventory_turnover'] = df[unicode('营业成本')] / zero_to_nan(df['average_inventory'])
        df['inventory_turnover'] = df['inventory_turnover'].apply(lambda x: None if x == 0 else x)
        # 存货周转率（天
        df['inventory_days'] = 360 / zero_to_nan(df['inventory_turnover'])

    # 偿债能力
    # 流动比率=流动资产÷流动负债
    # 速动比率=(流动资产合计－库存)/ 流动负债
    # 资产负债率
    if not is_financial_stock:
        df['current_ratio'] = df[unicode('流动资产合计')] / zero_to_nan(df[unicode('流动负债合计')])
        df['quick_ratio'] = (df[unicode('流动资产合计')] - df[unicode('存货')]) / zero_to_nan(df[unicode('流动负债合计')])
        df['al_ratio'] = df[unicode('负债合计')] / zero_to_nan(df[unicode('资产总计')])

    # 现金流量
    if not is_financial_stock:
        df['cf_sales'] = df[unicode('经营活动产生的现金流量净额')] / zero_to_nan(df[('main_biz_income')])
        df['cf_nm'] = df[unicode('经营活动产生的现金流量净额')] / zero_to_nan(df[('net_profit')])
        # 3费在营业成本占比
        df['three_expenses_ratio'] = (df['selling_expenses'] + df['financial_expenses']
                                      + df['management_expenses']) / zero_to_nan(df['main_biz_cost'])
    return df


def get_max_period_from_mongo(code):
    max_period_cursor = db['stock_period_indices'].aggregate([
        {"$match": {"code": code}},
        {"$group": {"_id": '$code', "max": {"$max": "$period"}}}
    ])
    result = list(max_period_cursor)
    if len(result) == 1:
        max = result[0]
        return max['max'];
    return None

def get_max_period_from_mysql(code):
    cursor = mysqlDB.query("select max(period) from newlove_stock_period_indices"
                           " where code=%s", [code])
    max_period = cursor.fetchone()[0]
    return max_period

def save_to_mongodb(period_indices):
    one = db['stock_period_indices'].find_one({'code': period_indices['code'], 'period': period_indices['period']})
    if not one:
        db['stock_period_indices'].insert_one(period_indices)
    else:
        Utils.logger.warning("period indices already exits in mongo " + period_indices['code'] + "," + period_indices['period'])

def save_to_mysql(period_indices):
    max_day_cursor = mysqlDB.query("select count(*) from newlove_stock_period_indices where code = %s and period = %s",
                                   [period_indices['code'], period_indices['period']])
    count = max_day_cursor.fetchone()[0]
    if count == 0:
        mysqlDB.insert("newlove_stock_period_indices", period_indices)
    else:
        Utils.logger.warning("period indices already exits in mysql " + period_indices['code'] + "," + period_indices['period'])

# https://pypi.org/project/simplemysql/
def collect(stocks):
    for stock in stocks[:]:
        if Utils.bypass_stock(stock['code']):
            Utils.logger.info(stock['code'] + " bypassed ")
            continue
        try:
            Utils.logger.info("--->calc stock period indices of " + stock["name"]+ "," + stock["code"])
            stockCode = stock['code']
            code = stock['code']

            max_period_from_mongo = get_max_period_from_mongo(code)
            max_period_from_mysql = get_max_period_from_mysql(code)

            Utils.logging.info("max period of stock "
                               + stock['code'] + ": mongo:"
                               + str(max_period_from_mongo) + ", mysql:" + str(max_period_from_mysql))

            df = calc_stock_indices(code)
            if df is None:
                Utils.logging.info("fail to calc stock period indices of " + stock['code'])
                continue
            df = df.fillna(0)

            #对于金融stock 某些值不计算，所以默认值代替
            def get_value(series, name, defaultValue):
                if name in series.index:
                    return series[name]
                else:
                    return defaultValue

            save_to_mongo_count = 0
            save_to_mysql_count = 0

            for period, row in df.iterrows():
                '''if max_period is not None and period <= max_period:
                    Utils.logger.info("period (" + period + ") indices of stock " +stockCode + " already exists in mysql")
                    continue'''
                record = {}
                record['code'] = code
                record['period'] = period
                record['name'] = stock['name']
                record['roe'] = row['roe']
                record['roa'] = row['roa']
                record['roic'] = get_value(row, 'roic', 0)
                record['gpr'] = get_value(row, 'gpr', 0)
                record['npr'] = row['npr']

                # 成长能力
                record['mbg_rate'] = row['mbg_rate']
                record['npg_rate'] = row['npg_rate']
                record['nag_rate'] = row['nag_rate']
                record['tag_rate'] = row['tag_rate']

                # 运营能力
                record['ar_turnover'] = get_value(row, 'ar_turnover', 0)
                record['ar_days'] = get_value(row, 'ar_days', 0)
                record['inventory_turnover'] = get_value(row, 'inventory_turnover', 0)
                record['inventory_days'] = get_value(row, 'inventory_days', 0)


                # 偿债能力
                record['current_ratio'] = get_value(row, 'current_ratio', 0)
                record['quick_ratio'] = get_value(row, 'quick_ratio', 0)
                record['al_ratio'] = get_value(row, 'al_ratio', 0)

                # 现金流量
                record['cf_sales'] = get_value(row, 'cf_sales', 0)
                record['cf_nm'] = get_value(row, 'cf_nm', 0)

                # 3费占比
                record['three_expenses_ratio'] = get_value(row, 'three_expenses_ratio', 0)

                record['total_assets'] = row['total_assets']
                record['net_assets'] = row['net_assets']
                record['net_assets_opc'] = row['net_assets_opc']
                record['net_profit'] = row['net_profit']
                record['net_profit_koufei'] = row['net_profit_koufei']
                record['net_profit_opc'] = row['net_profit_opc']
                record['main_biz_income'] = row['main_biz_income']
                record['main_biz_cost'] = row['main_biz_cost']
                record['total_shares'] = row['total_shares']
                record['selling_expenses'] = get_value(row, 'selling_expenses', 0)
                record['financial_expenses'] = get_value(row, 'financial_expenses', 0)
                record['management_expenses'] = get_value(row, 'management_expenses', 0)
                record['createTime'] = datetime.now()
                record['modifyTime'] = datetime.now()
                #mysqlDB.insert("newlove_stock_period_indices", record)
                #print(record.keys())
                if max_period_from_mongo is None or period > max_period_from_mongo:
                    save_to_mongodb(record.copy())
                    save_to_mongo_count = save_to_mongo_count + 1
                if max_period_from_mysql is None or period > max_period_from_mysql:
                    save_to_mysql_count = save_to_mysql_count + 1
                    save_to_mysql(record.copy())
            mysqlDB.commit()
            Utils.logger.info("save to mysql and mongo count:" + str(save_to_mysql_count) + "," + str(save_to_mongo_count))
            Utils.logger.info("--->success calc stock period indices of " + stock["name"]+ "," + stock["code"] + "\n")
        except Exception as es:
            traceback.print_exc()
            Utils.logger.error("fail to calc stock period indices of " + stock['name'] + "," + stock["code"] + "\n")


#中信证券似乎有问题
#stocks=[{"code":'600009', 'name':'上海机场'}]
#collect(stocks)
def main():
    stocks = Utils.quick_access_cursor(db['stock'].find({"code": {"$regex": "^00"}}, projection={"code": 1, "name": 1}, sort=[("code", 1)]));
    collect(stocks)
    stocks = Utils.quick_access_cursor(db['stock'].find({"code": {"$regex": "^30"}}, projection={"code": 1, "name": 1}, sort=[("code", 1)]));
    collect(stocks)
    stocks = Utils.quick_access_cursor(db['stock'].find({"code": {"$regex": "^60"}}, projection={"code": 1, "name": 1}, sort=[("code", 1)]));
    collect(stocks)

main()
Utils.logger.info("CalcFinanceData.py exit")
